﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace NKD07.Classes
{
    public class product:database
    {
        private int id;

        public int Id
        {
          get { return id; }
          set { id = value; }
        }

        private string name;

        public string Name
        {
          get { return name; }
          set { name = value; }
        }

        private string imageUrl;

        public string ImageUrl
        {
          get { return imageUrl; }
          set { imageUrl = value; }
        }

        private string description;

        public string Description
        {
          get { return description; }
          set { description = value; }
        }

        private int idCategory;

        public int IdCategory
        {
          get { return idCategory; }
          set { idCategory = value; }
        }

        private int stock;

        public int Stock
        {
          get { return stock; }
          set { stock = value; }
        }

        private float priceSell;

        public float PriceSell
        {
          get { return priceSell; }
          set { priceSell = value; }
        }

        private int numberLike;

        public int NumberLike
        {
          get { return numberLike; }
          set { numberLike = value; }
        }

        private int numberDislike;

        public int NumberDislike
        {
          get { return numberDislike; }
          set { numberDislike = value; }
        }

        private int idManufacturer;

        public int IdManufacturer
        {
          get { return idManufacturer; }
          set { idManufacturer = value; }
        }

        private DateTime dateAdded;

        public DateTime DateAdded
        {
          get { return dateAdded; }
          set { dateAdded = value; }
        }

        private string mapping;

        public string Mapping
        {
            get { return mapping; }
            set { mapping = value; }
        }

        // get all products from database
        public DataTable getNewProducts()
        {
            DataTable products = new DataTable();
            OpenConnection();
            if (Connection != null)
            {
                string queryString = "select top 8 * from Product pr, Manufacturer manu  where manu.id=pr.idManufacturer order by pr.dateAdded desc";
                try
                {
                    SqlDataAdapter adapter = new SqlDataAdapter(queryString, Connection);
                    adapter.Fill(products);
                }
                catch (SqlException)
                {
                    products = new DataTable();
                }
            }
            return products;
        }


        public DataTable GetProductByID(int productID)
        {
            DataTable product = new DataTable();
            OpenConnection();
            if (Connection != null)
            {
                string queryString = "select * from Product pr, Category cat, Manufacturer manu  where pr.idCategory=cat.id and manu.id=pr.idManufacturer and pr.id=" + productID.ToString();
                try
                {
                    SqlDataAdapter adapter = new SqlDataAdapter(queryString, Connection);
                    adapter.Fill(product);
                }
                catch (SqlException)
                {
                    product = new DataTable();
                }
            }
            return product;
        }

        public DataTable GetProductsByCategoryID(int categoryID, int option)
        {
            DataTable product = new DataTable();
            OpenConnection();
            if (Connection != null)
            {
                string queryString = "";
                if(option==0)
                    queryString = "select * from Product pr, Category cat, Manufacturer manu  where pr.idCategory=cat.id and manu.id=pr.idManufacturer"
                        +" and cat.id=" + categoryID.ToString()+" order by pr.name asc";
                else if (option == 1)
                    queryString = "select * from Product pr, Category cat, Manufacturer manu  where pr.idCategory=cat.id and manu.id=pr.idManufacturer"
                        + " and cat.id=" + categoryID.ToString() + " order by pr.priceSell asc";
                else
                    queryString = "select * from Product pr, Category cat, Manufacturer manu  where pr.idCategory=cat.id and manu.id=pr.idManufacturer"
                        + " and cat.id=" + categoryID.ToString() + " order by pr.dateAdded asc";
                try
                {
                    SqlDataAdapter adapter = new SqlDataAdapter(queryString, Connection);
                    adapter.Fill(product);
                }
                catch (SqlException)
                {
                    product = new DataTable();
                }
            }
            return product;
        }

        // delete product based on its id
        public bool DeleteProductByID(int productID)
        {
            bool check = false;
            OpenConnection();
            if (Connection == null)
                return false;
            try
            {
                Connection.Open();
                string cmdText = "Delete from Product Where ID=@ID";
                SqlCommand cmd = new SqlCommand(cmdText, Connection);
                cmd.Parameters.Add(new SqlParameter("@ID", id));
                cmd.ExecuteNonQuery();
                check = true;
            }
            catch (SqlException)
            {
                check = false;
            }
            finally
            {
                CloseConnection();
            }
            return check;
        }

        //update informations of product
        public bool UpdateProductInfo(product product)
        {
            bool result = false;
            OpenConnection();
            if (Connection != null)
            {
                try
                {
                    Connection.Open();
                    string cmdText = "";
                    if (product.ImageUrl.CompareTo("") != 0)
                    {
                        cmdText = "update Product set name=@name,imageUrl=@imageUrl,description=@description," +
                        "idCategory=@idCategory,stock=@stock,priceSell=@priceSell,numberLike=@numberLike,numberDislike=@numberDislike," +
                        "idManufacturer=@idManufacturer,dateAdded=@dateAdded,Mapping=@Mapping where id=@id";
                    }
                    else
                    {
                        cmdText = "update Product set name=@name,description=@description,idCategory=@idCategory,"+
                        "stock=@stock,priceSell=@priceSell,numberLike=@numberLike,numberDislike=@numberDislike," +
                        "idManufacturer=@idManufacturer,dateAdded=@dateAdded,Mapping=@Mapping where id=@id";
                    }
                    SqlCommand cmd = new SqlCommand(cmdText, Connection);

                    SqlParameter param = new SqlParameter("name", product.Name);
                    cmd.Parameters.Add(param);

                    if (product.ImageUrl.CompareTo("") != 0)
                    {
                        param = new SqlParameter("imageUrl", product.ImageUrl);
                        cmd.Parameters.Add(param);
                    }

                    param = new SqlParameter("description", product.Description);
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("idCategory", product.IdCategory);
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("stock", product.Stock);
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("priceSell", product.PriceSell);
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("numberLike", product.NumberLike);
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("numberDislike", product.NumberDislike);
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("idManufacturer", product.IdManufacturer);
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("dateAdded", product.DateAdded);
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("Mapping", product.Mapping);
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("id", product.Id);
                    cmd.Parameters.Add(param);

                    cmd.ExecuteNonQuery();

                    result = true;

                    CloseConnection();
                }
                catch (SqlException)
                {
                    CloseConnection();
                    result = false;
                }
            }
            return result;
        }

        internal bool InsertProduct(product product)
        {
            bool result = false;
            OpenConnection();
            if (Connection != null)
            {
                try
                {
                    Connection.Open();
                    string cmdText = "insert into Product(name,imageUrl,description," +
                    "idCategory,stock,priceSell,numberLike,numberDislike," +
                    "idManufacturer,dateAdded,Mapping) values(@name,@imageUrl,@description," +
                    "@idCategory,@stock,@priceSell,@numberLike,@numberDislike," +
                    "@idManufacturer,@dateAdded,@Mapping)";
                    SqlCommand cmd = new SqlCommand(cmdText, Connection);

                    SqlParameter param = new SqlParameter("name", product.Name);
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("imageUrl", product.ImageUrl);
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("description", product.Description);
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("idCategory", product.IdCategory);
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("stock", product.Stock);
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("priceSell", product.PriceSell);
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("numberLike", product.NumberLike);
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("numberDislike", product.NumberDislike);
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("idManufacturer", product.IdManufacturer);
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("dateAdded", product.DateAdded);
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("Mapping", product.Mapping);
                    cmd.Parameters.Add(param);

                    cmd.ExecuteNonQuery();

                    result = true;

                    CloseConnection();
                }
                catch (SqlException)
                {
                    CloseConnection();
                    result = false;
                }
            }
            return result;
        }
    }
}